Accounts is a Claris FileMaker Pro™ 2.1 file, distributed as shareware. If you find it useful, please send $20 to Icicle Computer by cheque or Visa at one of the addresses above.
Entries to this database balance to zero as an equal debit and credit is entered by each record to the accounts of choice. The file constantly generates accurate account statements as well as current net worth and gain/loss during current operations.
BEFORE YOU START YOUR OWN FILE! Accounts is distributed with sample data to provide a coherent demonstration of the file; TAKE CARE to delete all of these sample records, OR make a clone of the demonstration (especially the Accounts and Post-dated Items files) when you start use the file for your own records. Until then, use this file with the sample data as a demonstration.
The accounts kept are personal chequing, savings and credit cards, as well as investment, RRSP, US dollar, loans and mortgages. A “Capital” account keeps track of net worth. A proprietary “business” is included. It has an individual focus—the business is modest and lacks systems for payables, receivables or employees.
Operating Principles
A “$ Flow” quasi-account is used to track transactions with the rest of the world. In the course of operation, the balance of $ Flow is the amount of profit or loss during the period. Note that positive net worth is represented by negative value in Capital, and good results during the past while are shown as a substantial negative $ Flow!
In detail, the accounts set up for use are:
$ Flow—the link between you and the rest of the world.
PCA—a Personal Chequing Account
US$—a foreign currency deposit account
Credit Card—the place to record all those chits generated by the plastic
Savings Acct
Business Account—a current account, if you have a business
Receivables—for all that money you hope will come…
Loan - Demand—typically, any loan to you payable on demand
Loan - L/C—a line of credit at the bank
Mortgage
Portfolio Cash—an investment account with a dealer
Portfolio Market—the cost of investments now held
RRSP Cash—an investment account for a Registered Retirement Savings Plan
RRSP Market—the cost of investments held in an RRSP
RESP—a Registered Education Savings Plan for your kids
Business Eqpt—depreciating value of appropriate machinery and equipment
Car—the depreciating value of your mobile asset
House—the cost of your real estate (or an estimate of its value)
Capital—your net worth, determined by all of the above.
(Text descriptions in layouts can be quickly changed using ordinary FileMaker layout practices to suit your preference, without bothering about the underlying field definitions. On the other hand, change these account names in fields only with thorough care, as many calculated fields depend upon their exact spelling in the ‘To’ and ‘From’ fields. Changing the names is not difficult, but does require patience to ensure that all affected field calculations are changed. )
The database requires only a few fields of data for each record:
• from
• to
• amount.
Each record must specify to which account money moves, from which account (it may move in and out of the same account). The amount, obviously, is the reason for doing all this. A date is basic, and space for a description and a reference (cheque, invoice, whatever) number is included.
As well, the database includes a field in each record to identify categories of expense/revenue. ‘Line numbers’ and their associated descriptions are included in the file called “Lines.” When you enter a line number in the data entry layout, the database looks up the description in the Lines file and enters it to Bookkeeper. The automatic entry will over-write anything which has previously been entered to Bookkeeper’s “description” field, but the text can be changed in any way when the look-up is complete. To make a copy of the list of numbers and categories, open Lines and use the ‘Print the List’ script. If the numbers and descriptions provided do not meet your needs, they can be easily changed to suit particular circumstances or history. Some ‘find’ routines included in scripts in Accounts use the numbers provided, but these can be changed using ordinary FileMaker techniques.
You can go back and adjust or delete any entry at any time. This file is oriented to people responsible for their own accounting. It does not try to impose audit controls or trails of any kind.
Layouts in (and all other aspects of) these databases can be changed to suit your taste, using standard FileMaker techniques.
Juggling the Books
To do things with the Accounts, Lines, Post-dated Items and Inventory files, you can either clone the files into any folder and start from scratch, or delete records and use the files provided. In Accounts, the ‘Control Centre’ layout is intended as the principal operating place. The buttons on the right-hand side of the layout, and a number of scripts found in the Scripts menu, carry out the basic operations.
Choose a convenient date to establish your accounts. Have a look at the model entries in Accounts, which demonstrate opening balances for the various accounts. Your existing records—or a wild guess—provide the amounts of opening balances. For assets (such as chequing or savings accounts or the value of real estate or cars), start by making a record which draws the amount from Capital and to the account. Liabilities (credit card charges, mortgages, loans and the like) are the opposite, from the account to Capital.
Entries—A typical entry might be payment of a hydro bill. From the Control Centre layout, click on the ‘Entries’ button. The ‘Data Entry’ layout appears, ready for new data in a new record. In the first (line number) field, type in the appropriate category (1310 for hydro), and <tab> to the next field (reference) and optionally enter a cheque number or other piece of information. Tab to the ‘date’ field next, changing it if necessary, then on to the ‘description’ field. Note that the lookup file has provided a basic description for records with line numbers. Edit the description as required, and <tab> to the ‘From’ field. From the pop-up menu, choose the account from which the payment is made, for instance, PCA. <Return> enters the account name and moves on to the next, ‘To’ field. The same process enters the account to which payment goes. If the payment is going to an outside person, company or whatever, it’s going to ‘$ Flow.’ Next, and usually last, enter the amount involved. In the special case of receiving an amount of cash and not depositing it to any account, a final field allows the amount to be specified as “revenue.” Using that qualifier as needed ensures that expense reports are accurate. The record is now complete. Another new record can be called automatically by clicking the <Continue> button, or pressing the <Enter> key. The ‘Entries’ script can be discontinued by clicking the <Cancel> button or by pressing <command>-<.> or <Escape>.
The same basic process applies for all entries. For consistency, you should consider that cash usually belongs to $ Flow. If you withdraw funds from a bank or sock or whatever, the money would move from PCA or Savings to $ Flow.
Just as payments to outside persons or companies are to $ Flow, so payments received from others, such as salary or interest or money found on the sidewalk, are from $ Flow. If you pay cash for your lunches, the payment is from $ Flow, to $ Flow. With a line number! (You can gather payments, for instance for groceries or bus tickets, in one record from time to time according to your own needs. You don’t need a separate record for every tiny cash payment; as FileMaker isn’t the speediest application going, you’ll do yourself a favour by adding records in moderation!)
The easiest way to deal with a single payment made for items in several categories is to make the single payment from whatever source account to $ Flow, without recording any category line number, then to make as many separate records of transactions as needed from and to $ Flow in the appropriate line numbers.
(While Accounts could operate over a period of years, you may wish to close your books once a year and carry the balances of accounts forward. For instance, consider a new Accounts file for each calendar year. Carry on business as usual until sometime in January to catch late entries, then transfer to a clone of the database for next year. When you do that, use the process described above (account balances drawn from or to Capital) to re-establish the Capital value, using values from the General Ledger listing of accounts as at the day of the transfer (‘find’ records of that date or earlier, then select the “General Ledger” layout). You might consider describing each one as a ‘Balance forward’ instead of an ‘Opening balance,’ but otherwise the process is the same.)
You can depreciate the value of selected assets by periodically (annually, probably) making a record transferring the amount of depreciation from, for instance, ‘Car’ to $ Flow. The amount is up to you, but Revenue Canada publishes various rates as part of its Capital Cost Allowance report. Cars and computers depreciate at 30% per year, furniture and appliances at 20%.
Items Due—The file called “Post-dated Items” can be used as a place to maintain scheduled debits or credits, whether post-dated cheques or payments due, or regular receipts such as salary payments. The button labelled ‘Due’ in the Accounts file does the work. It finds all items in Post-dated Items which are dated on or before today, imports them to Accounts, and finally deletes the found set from Post-dated Items.
CAUTION! If the ‘Due’ button finds nothing due, it puts up a dialogue box telling you that and asking whether you wish to continue the script. Click the CANCEL button, which will return you to the open Post-dated Items file. Close that, and you’re back in Accounts. (Troubleshooting: If you inadvertently click ‘Continue,’ the script will transfer all future items due to Accounts then delete the records in Post-dated Items. In that case, you can import them (they become the ‘found set’ in Accounts) back to Post-dated Items. Then, delete them from Accounts.)
You can add new records to Post-dated Items by making, duplicating and editing records directly, or by importing a file from, for instance, a spreadsheet.
Inv’try—The Inventory button finds and lists goods bought in the current Accounts file, and offers the list for editing. The edited list can then be imported to a separate “Inventory” file. For those who move frequently or have any reason at all to maintain a current inventory, this semi-automatic process can save much time!
Data—In various layouts, a button labelled ‘Data’ allows an automatic move to and from the Data Entry layout to make changes.
Reports and Statements
Reports and statements are automatically generated by the other buttons on the Control Centre.
GL—This general ledger summary gives you an overall view of your current situation. Here’s where the double-entry balance of credits and debits, assets and liabilities, is most evident. Note that the total of assets equals the total of liabilities and capital. (If it doesn’t, something is very wrong! “Find” the records which lack either a ‘To’ or ‘From’ account and complete them to fix an imbalance.)
Lines—This breaks down your revenue and expenses by ‘line number’ categories. This information can be printed as a report, or exported for use, for example, in a spreadsheet budget model.
Loan—displays the weight of current liabilities.
Cards—itemizes credit card records. The statement informs you of the total amount still outstanding but it does not try to balance to any credit card account. Instead, it treats all credit cards as part of a single account.
PCA, US$, Savings, and Business—these are bank accounts, typically. By ticking off the items reported on your bank statements (type “y” in the column under the tick mark, just to the left of the “debit” column), the various statements will automatically display a reconciliation balance which should (!) agree with the bank. If it doesn’t, check those amounts again and make sure that only items received by the bank are marked ‘yes’ and that any charges or interest payments are included; if the values in Accounts agree with the values in the printed statements from your bank, and if all of the cheques/withdrawals and deposits noted by the bank are marked ‘yes’ in Accounts, the two must reconcile.
(About US$ or other foreign currency accounts—this database is written from a Canadian perspective. US and Canadian dollars (or Swiss francs or British pounds) are more or less the same value, so we pretend that they are identical within the US$ account. If we convert between US$ and C$, the foreign exchange differential is noted as expense or revenue on line 2800. Over time (unless you’re a successful foreign currency speculator) that amount probably hovers around zero. But it does keep things in balance.
For tinkerers only—if you happen to have an account in a currency whose unit differs from the dollar by an order or two of magnitude, such as yen or lire, pretending equivalency may not work so well. You could fiddle appearances through the field definitions, defining a yen credit or debit or balance, for instance, as the amount divided by 100, or a lire amount by 1000. Not perfect perhaps, but a reasonable approximation.)
P’folio Cash and RRSP Cash—are modelled on investment accounts. These, too, will automatically reconcile with account statements from the dealer. (RRSP=Registered Retirement Savings Plan, a Canadian savings/tax deferral vehicle.)
P’folio Market and RRSP Market—are amounts invested.
The periodic statement from the dealer should reconcile to ‘Portfolio Cash’ or ‘RRSP Cash,’ while the cost value of holdings is reflected in the ‘Portfolio Market’ or ‘RRSP Market’ accounts. Portfolio holdings of stocks and bonds (which may be kept in a safety deposit box or in segregation at a brokerage) are intended to be kept separate from the current cash in an account with an investment dealer. Tracking the current value of holdings is beyond the scope of Accounts.
Typically, a stock purchase needs two records; one is the actual value of the shares (or whatever) from Portfolio (or RRSP) Cash to Portfolio (or RRSP) Market. The second is the commission plus charges from Portfolio (or RRSP) Cash to $ Flow. A sale would include first the actual sale value from Portfolio (or RRSP) Market to Portfolio (or RRSP) Cash, and a second record of the commission plus charges from Portfolio (or RRSP) Cash to $ Flow. A sale is further complicated by capital gain or loss. If you held the investment for a short time, you may have a record of the original cost in the current file, but often that isn’t the case, and you will need to refer to older records to discover the original cost. Using old fashioned manual methods, subtract the cost from sale value of the investment, and enter that amount as a record of capital gain from $ Flow to Portfolio (or RRSP) Market, or capital loss from Portfolio (or RRSP) Market to $ Flow.
If you maintain an investment portfolio, you could establish a parallel, ongoing statement of account along the lines of the Inventory file which would import records dealing with the Portfolio and RRSP and would enable you to find older records of purchases without undue difficulty. Such a statement can easily be made to report the long-term capital gain or loss which you have experienced.
Scripts
Simply List Found Set—just sorts and displays a simple list of the found set. This is included mostly for convenience when you are first browsing the file, and wonder what on earth is going on.
Preview Lines List—finds all of the items included in all expense/revenue lines and previews the detailed list, ready for printing.
LO Summary Report (to edit), and LO Summary Report (preview)—allow you to find all of your expense and revenue, edit the list (by selectively omitting records), and to preview (and, if you wish, to print) a summary of expenses by category. (By the way, the layout ‘Line Summary List’ has no body; it uses only summary parts. Therefore, nothing appears on it in a ‘Browse’ view.)
Export LO Summary (after edit)—can be used to export the summaries by line to another file for use as you need, for instance in a spreadsheet. By default, it saves a tab-separated text file named “Junk” on the desktop. That can be changed in the ScriptMaker.
Business’ Year—finds, previews and prints the detailed results of revenue and expenses included in lines numbered 2600-2699.
Export Business Lines—exports the summary results of lines 2600-2699. Again, a tab-separated text file named “Junk” on the desktop.
Tax Lines #1 (ready to edit), and Tax Lines #2 (after editing)—find, allow editing and list all of the personal, not business, items occurring under lines which probably have some tax relevance, such as net pay or child support. (You can see which lines #1 checks by choosing the ‘Simple List’ layout after running the script, then selecting ‘Refind’ from the ‘Select’ menu. If you wish, change the find requests as you need, do the find, and then edit the ‘Tax Lines #1’ script, just replacing the find requests.)
Inventory Items—finds and lists all of the items bought under lines which logically are goods. Stops, ready for editing (by selective omission). As for ‘Tax Lines,’ the find requests are easily edited in order to modify the scripts.
Transfer Inventory—calls a script in the Inventory file to import the records identified in the ‘Inventory Items’ script.
The Inventory file offers fields to describe, enumerate and value objects. If you import records from the Accounts file, you should enter the appropriate ‘quantity’ of each item, and you may wish to add serial numbers in the ‘Item No.’ field. The FileMaker ‘Replace’ command will allow you to do that, starting from the next number higher than those already in the file. If you make many manual changes to the inventory, you may wish to define the field (‘Serial’) options to allow automatic entry of a serial number.
Incidentally, Inventory revalues the replacement cost of your goods upwards by 3% per year. You can adjust that inflation factor in the ‘Current value’ field definition.
Disclaimer
This software is a generic tool for use with a computer. No warranty of its suitability for any particular purposes or circumstances is made or implied. We hope that you find it helpful.
